/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package functions;

import GUI.MainFrame;
import java.sql.Date;
import model.*;
import model.User;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

/**
 *
 * @author JArik
 */
public class Users {




    private String query;
    private boolean groupName;
    public Users()
    {
        query = "1=1 AND users.deleted=0 ";
        groupName=false;
    }



    public void groupByName() {
        groupName=true;
    }
    public void searchByName(String name)
    {
        query += "AND (CONCAT(name,' ',surname) LIKE '%"+name+"%' OR  CONCAT(surname,' ',name) LIKE '%"+name+"%')";
    }
    public void searchByAddress(String address)
    {
        query +="AND address LIKE '%"+address+"%' ";
    }
    public void searchByBreed(String breed)
    {
        query +="AND breed LIKE '%"+breed+"%' ";
    }
    public void searchByNick(String nick)
    {
        query +="AND nick LIKE '%"+nick+"%' ";
    }
    public ArrayList<User> startSearch() throws SQLException
    {
        Database db = new Database();
        if (groupName) query += " GROUP BY users.id";
        ResultSet set = db.getDBInfo("users.id,name,surname, dogs.id, dogs.age AS dogAge, " +
                                     "dogs.breed, dogs.nick,users.address, users.email, tel, " +
                                     "dogs.deathDate ",
                "users LEFT JOIN dogs ON dogs.userId = users.id AND dogs.deleted =0", query);
        ArrayList<User> list = new ArrayList<User>();
        while ( set.next())
        {
            User user;
             //Date dt = set.getDate(5);
             user = new User(set.getInt("id"),     // id
                              set.getString("name"),  // name
                              set.getString("surname"),  // surname
                              set.getInt(4) == 0 ? null:
                              new Dog(set.getInt(4),
                                      set.getDate(5),
                                      set.getDate("deathDate"),
                                      set.getInt(1),
                                      set.getString(6),
                                      set.getString(7))
                              );
            user.setAddress(set.getString(8));
            user.setEmail(set.getString("email"));
            user.setTelef(set.getString("tel"));
            list.add(user);
            
        }
        db.closeStatement();
        return list;
    }
    
    public static ArrayList<User> getAll() throws SQLException
    {
        Database db = new Database();
        ResultSet set = db.getDBInfo("id,name,surname", "users","");
        ArrayList<User> list = new ArrayList<User>();
        while ( set.next())
        {
            list.add(new User(set.getInt(1),     // id
                              set.getString(2),  // name
                              set.getString(3)  // surname
                              ));
        }
        db.closeStatement();
        return list;
    }
    public static String getAddressById(int id) throws SQLException
    {
        Database db = new Database();
        ResultSet set = db.getDBInfo("address", "users","id ="+id);
        String address = "";
        if(set.next())
            address = set.getString("address");
        db.closeStatement();
        return address;
    }
    public static User getById(int id) throws SQLException
    {
        Database db = new Database();
        ResultSet set = db.getDBInfo("id,name,surname", "users","id ="+id);
        User user = null;
        if(set.next())
            user = new User(set.getInt("id"),     // id
                              set.getString("name"),  // name
                              set.getString("surname")  // surname
                              );
        db.closeStatement();
        return user;
    }
    public static ArrayList<User> getByBreed(String breed) throws SQLException
    {
        Database db = new Database();
        ResultSet set = db.getDBInfo("id,name,surname", "users","breed ="+breed);
        ArrayList<User> list = new ArrayList<User>();
        while ( set.next())
        {
            list.add(new User(set.getInt(1),     // id
                              set.getString(2),  // name
                              set.getString(3)  // surname
                             // set.getInt(4)      // breed
                              ));
        }
        db.closeStatement();
        return list;
    }
    public static ArrayList<User> getByName(String name) throws SQLException
    {
        Database db = new Database();
        ResultSet set = db.getDBInfo("id,name,surname", "users","name LIKE '%" + name + "%' ");
        ArrayList<User> list = new ArrayList<User>();
        while ( set.next())
        {
            list.add(new User(set.getInt(1),     // id
                              set.getString(2),  // name
                              set.getString(3)  // surname
                              ));
        }
        db.closeStatement();
        return list;
    }

    public static int addNewUser(String name, String surname, String address,
                                 String email,String tel) throws SQLException
    {
        Database db = new Database();
        int res = 0;
        String what = "", values ="SELECT ";
        if(!surname.isEmpty())
        {
            what +="surname";
            values +="'"+surname+"' ";
        } else
            throw new SQLException("Укажите фамилию");

        if(!name.isEmpty())
        {
            what +=",name";
            values +=", '"+name+"' ";
        } else
        {
            what +=",name";
            values +=", '' ";
        }

        if(!address.isEmpty())
        {
            what +=",address";
            values +=",'"+address+"' ";
        }
        if(!email.isEmpty())
        {
            what +=",email";
            values +=",'"+email+"' ";
        }
        if(!tel.isEmpty())
        {
            what +=",tel";
            values +=",'"+tel+"' ";
        }
        what +=", oldId";
        values +=", (SELECT MIN(oldId) FROM users)-1";
        //INSERT INTO users(surname, oldId,name)
        //SELECT DISTINCT 'test2',(SELECT MIN(oldId) FROM users)-1,'test' FROM users LIMIT 1;

        ResultSet set = db.insertDBInfo("INSERT INTO users ("+what+") "+
                                        values);
        if(set.next())
            res = set.getInt(1);
        db.closeStatement();
        return res;
    }

    public static int changeUser(int userId,
                                 String name, String surname, String address,
                                 String email,String tel) throws SQLException
    {
        Database db = new Database();
        int res = 0;
        if(userId <= 0 )
            throw new SQLException("Неправельный идентификатор.");

        res = db.updateDBInfo("users",
                                        "name='"+name+"',"+
                                        "surname='"+surname+"',"+
                                        "address='"+address+"',"+
                                        "email='"+email+"',"+
                                        "tel='"+tel+"'"
                                        ,"id="+userId);
        db.closeStatement();
        //if(res == 1)
          return userId;
        //else throw new SQLException("Ошибка, невозможно изменить инфомацию.");
    }
    public static boolean uniteUsers(int mainId, ArrayList<Integer> uniteIds)
                                                            throws SQLException
    {
        Database db = new Database();
        if(mainId <= 0)
            throw new SQLException("Выберите основного клиента");

        String users = "userId="+uniteIds.get(0);
        for(int i=1; i<uniteIds.size(); i++)
            users += " OR userId="+uniteIds.get(i)+" ";
        
        int res = db.updateDBInfo("UPDATE dogs SET userId="+mainId+" WHERE "+users);

        db.closeStatement();
        //if(res == 1)
          return true;
        //else throw new SQLException("Ошибка, невозможно изменить инфомацию.");

    }
    public static void delete(ArrayList<Integer> toDelete) throws SQLException
    {
        Database db = new Database();
        if(toDelete.size() <=0)
            throw new SQLException("Выберите клиента для удаления.");
        
        String users = "id="+toDelete.get(0);
        for(int i=1; i<toDelete.size(); i++)
            users += " OR id="+toDelete.get(i)+" ";

        int res = db.updateDBInfo("UPDATE users SET deleted=1 WHERE "+users);
        db.closeStatement();
    }
}
